package com.stark.commons.lang.jdbc;

import lombok.Cleanup;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库访问对象。
 * <p>对 JDBC 的简单封装。
 * @author Ben
 * @since 1.0.0
 * @version 1.0.0
 */
@NoArgsConstructor
@Slf4j
public class DBAccess implements DBOperations {
	
	public static final int MAX_WAIT_SECONDS = 5;
	public static final int MAX_RECONNECT_TIMES = 3;
	public static final int MAX_LIVE_TIME_MILLIS = 1000 * 60 * 5;
	
	private String driverClassName;
	
	private String url;
	
	private String user;
	
	private String password;
	
	private boolean autoCommit = true;
	
	private int maxWaitSeconds = MAX_WAIT_SECONDS;
	
	private int maxReconnectTimes = MAX_RECONNECT_TIMES;
	
	private int maxLiveTimeMillis = MAX_LIVE_TIME_MILLIS;
	
	private Connection connection;
	
	private long connectTime;
	
	private boolean logError = true;
	
	/**
	 * System property that instructs Spring to ignore {@link java.sql.ParameterMetaData#getParameterType}
	 * completely, i.e. to never even attempt to retrieve {@link PreparedStatement#getParameterMetaData()}
	 * for {@link StatementCreatorUtils#setNull} calls.
	 * <p>The default is "false", trying {@code getParameterType} calls first and falling back to
	 * {@link PreparedStatement#setNull} / {@link PreparedStatement#setObject} calls based on well-known
	 * behavior of common databases. Spring records JDBC drivers with non-working {@code getParameterType}
	 * implementations and won't attempt to call that method for that driver again, always falling back.
	 * <p>Consider switching this flag to "true" if you experience misbehavior at runtime, e.g. with
	 * a connection pool setting back the {@link PreparedStatement} instance in case of an exception
	 * thrown from {@code getParameterType} (as reported on JBoss AS 7).
	 */
	private boolean shouldIgnoreGetParameterType = false;
	
	public DBAccess(boolean autoCommit) {
		this.autoCommit = autoCommit;
	}
	
	public DBAccess(DBOptions options) {
		this.autoCommit = options.isAutoCommit();
		this.maxWaitSeconds = options.getMaxWaitSeconds();
		this.maxReconnectTimes = options.getMaxReconnectTimes();
		this.maxLiveTimeMillis = options.getMaxLiveTimeMillis();
		this.logError = options.isLogError();
		this.shouldIgnoreGetParameterType = options.isShouldIgnoreGetParameterType();
	}
	
	/**
	 * 建立数据库连接。
	 * @param driverClassName 驱动类名。
	 * @param url 连接 URL 。
	 * @param user 用户名。
	 * @param password 密码。
	 * @return 是否成功。
	 */
	public boolean open(String driverClassName, String url, String user, String password) {
		this.driverClassName = driverClassName;
		this.url = url;
		this.user = user;
		this.password = password;

        return getConnection() != null;
	}
	
	/**
	 * 建立数据库连接。
	 * @param dbConfig {@link com.stark.commons.lang.jdbc.DBConfig} 对象。
	 * @return 是否成功。
	 * @see #open(String, String, String, String)
	 */
	public boolean open(DBConfig dbConfig) {
        return open(dbConfig.getDriverClassName(), dbConfig.getUrl(), dbConfig.getUser(), dbConfig.getPassword());
	}
	
	@Override
	public int queryForInt(String sql) throws SQLException {
		int integer = 0;
		
		@Cleanup Statement stmt = getConnection().createStatement();
		log.debug("==>  Preparing: {}", sql);
		ResultSet rs = stmt.executeQuery(sql);
		if (rs.next()) {
			integer = rs.getInt(1);
		}
		log.debug("<==  Total: {}", 1);
		
		return integer;
	}
	
	@Override
	public int queryForInt(String sql, Object... args) throws SQLException {
		int integer = 0;
		
		@Cleanup PreparedStatement ps = prepareStatement(sql, args);
		log.debug("==>  Preparing: {}", sql);
		ResultSet rs = ps.executeQuery();
		if (rs.next()) {
			integer = rs.getInt(1);
		}
		log.debug("<==  Total: {}", 1);
		
		return integer;
	}
	
	@Override
	public List<Map<String, Object>> queryForList(String sql) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<>();
		
		@Cleanup Statement stmt = getConnection().createStatement();
		log.debug("==>  Preparing: {}", sql);
		ResultSet rs = stmt.executeQuery(sql);
		ResultSetMetaData metaData = rs.getMetaData();
		
		Map<String, Object> map;
		while (rs.next()) {
			map = new LinkedHashMap<>();
			for (int i = 1; i <= metaData.getColumnCount(); i++) {
				map.put(metaData.getColumnLabel(i), rs.getObject(i));
			}
			list.add(map);
		}
		log.debug("<==  Total: {}", list.size());
		
		return list;
	}
	
	@Override
	public List<Map<String, Object>> queryForList(String sql, Object... args) throws SQLException {
		List<Map<String, Object>> list = new ArrayList<>();
		
		@Cleanup PreparedStatement ps = prepareStatement(sql, args);
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData metaData = rs.getMetaData();
		
		Map<String, Object> map;
		while (rs.next()) {
			map = new LinkedHashMap<>();
			for (int i = 1; i <= metaData.getColumnCount(); i++) {
				map.put(metaData.getColumnLabel(i), rs.getObject(i));
			}
			list.add(map);
		}
		log.debug("<==  Total: {}", list.size());
		
		return list;
	}
	
	/**
	 * 查询并使用传入的 {@link RowCallbackHandler} 处理每行 {@link ResultSet} 。
	 * @param sql SQL 语句。
	 * @param rch {@link ResultSet} 行处理器。
	 */
	public void query(String sql, RowCallbackHandler rch) throws SQLException {
		@Cleanup Statement stmt = getConnection().createStatement();
		log.debug("==>  Preparing: {}", sql);
		ResultSet rs = stmt.executeQuery(sql);
		
		while (rs.next()) {
			rch.processRow(rs);
		}
	}
	
	/**
	 * 查询并使用传入的 {@link RowCallbackHandler} 处理每行 {@link ResultSet} 。
	 * @param sql SQL 语句。
	 * @param args 参数列表。
	 * @param rch {@link ResultSet} 行处理器。
	 */
	public void query(String sql, Object[] args, RowCallbackHandler rch) throws SQLException {
		@Cleanup PreparedStatement ps = prepareStatement(sql, args);
		ResultSet rs = ps.executeQuery();
		
		while (rs.next()) {
			rch.processRow(rs);
		}
	}
	
	/**
	 * 执行插入或更新操作。
	 * @param sql SQL 语句。
	 * @return 影响行数。
	 */
	public int update(String sql) throws SQLException {
		int rows = 0;
		
		@Cleanup Statement stmt = getConnection().createStatement();
		log.debug("==>  Preparing: {}", sql);
		rows = stmt.executeUpdate(sql);
		log.debug("<==  Updates: {}", rows);
		
		return rows;
	}
	
	/**
	 * 执行插入或更新操作。
	 * <p><strong>需 jdbc4 支持！</strong></p>
	 * @param sql SQL 语句。
	 * @param args 参数列表。
	 * @return 影响行数。
	 */
	public int update(String sql, Object... args) throws SQLException {
		int rows = 0;
		
		@Cleanup PreparedStatement ps = prepareStatement(sql, args);
		rows = ps.executeUpdate();
		log.debug("<==  Updates: {}", rows);
		
		return rows;
	}
	
	/**
	 * 提交事务。
	 */
	public void commit() {
		try {
			connection.commit();
		} catch (SQLException e) {
			if (logError) {
				log.error("提交事务失败", e);
			}
		}
	}
	
	/**
	 * 回滚事务。
	 */
	public void rollback() {
		try {
			connection.rollback();
		} catch (SQLException e) {
			if (logError) {
				log.error("回滚事务失败", e);
			}
		}
	}
	
	/**
	 * 关闭连接。
	 */
	public void close() {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/**
	 * 获取数据库连接对象。
	 * @return 数据库连接对象。
	 */
	private Connection getConnection() {
		if (connection != null) {
			if (System.currentTimeMillis() - connectTime <= maxLiveTimeMillis) {
				return connection;
			}
			
			close();
		}
		
		if (maxReconnectTimes < 0) {
			while (connection == null) {
				try {
					Class.forName(driverClassName);
					DriverManager.setLoginTimeout(maxWaitSeconds);
					connection = DriverManager.getConnection(url, user, password);
					connection.setAutoCommit(autoCommit);
					connectTime = System.currentTimeMillis();
				} catch (Exception e) {
					connection = null;
					log.debug("无法连接数据库：driverClassName={},url={},user={}",
							driverClassName, url, user, e);
					
					try {
						Thread.sleep(1000);
					} catch (InterruptedException ex) {
						// do nothing
					}
				}
			}
			return connection;
		} else {
			for (int reconnectTimes = 0; reconnectTimes <= maxReconnectTimes; reconnectTimes++) {
				try {
					Class.forName(driverClassName);
					DriverManager.setLoginTimeout(maxWaitSeconds);
					connection = DriverManager.getConnection(url, user, password);
					connection.setAutoCommit(autoCommit);
					connectTime = System.currentTimeMillis();
					return connection;
				} catch (Exception e) {
					if (reconnectTimes == maxReconnectTimes && logError) {
						log.error("无法连接数据库：driverClassName={},url={},user={}",
								driverClassName, url, user, e);
					}
				}
			}
			return null;
		}
	}
	
	private PreparedStatement prepareStatement(String sql, Object... args) throws SQLException {
		PreparedStatement ps = connection.prepareStatement(sql);
		log.debug("==>  Preparing: {}", sql);
		if (args != null) {
			StringBuilder parameters = new StringBuilder();
			Object arg;
			for (int i = 0; i < args.length; i++) {
				arg = args[i];
				if (arg instanceof java.util.Date) {
					arg = new java.sql.Timestamp(((java.util.Date) arg).getTime());
				}
				
				if (arg != null) {
					ps.setObject(i + 1, arg);
				} else {
					setNull(ps, i + 1);
				}
				parameters.append(parameters.length() == 0 ? "" : ", ");
				if (arg != null) {
					parameters
						.append(arg)
						.append("(").append(arg.getClass().getSimpleName()).append(")");
				} else {
					parameters.append("null");
				}
			}
			log.debug("==>  Parameters: {}", parameters);
		}
		return ps;
	}
	
	/**
	 * 设空。
	 * @see org.springframework.jdbc.core.StatementCreatorUtils.setNull(PreparedStatement, int, int, String)
	 * @param ps preparedStatement
	 * @param paramIndex 参数索引
	 */
	private void setNull(PreparedStatement ps, int paramIndex)
			throws SQLException {
		boolean useSetObject = false;
		Integer sqlTypeToUse = null;
		if (!shouldIgnoreGetParameterType) {
			try {
				sqlTypeToUse = ps.getParameterMetaData().getParameterType(paramIndex);
			}
			catch (SQLException ex) {
				if (log.isDebugEnabled()) {
					log.debug("JDBC getParameterType call failed - using fallback method instead: " + ex);
				}
			}
		}
		if (sqlTypeToUse == null) {
			// Proceed with database-specific checks
			sqlTypeToUse = Types.NULL;
			DatabaseMetaData dbmd = ps.getConnection().getMetaData();
			String jdbcDriverName = dbmd.getDriverName();
			String databaseProductName = dbmd.getDatabaseProductName();
			if (databaseProductName.startsWith("Informix") ||
					(jdbcDriverName.startsWith("Microsoft") && jdbcDriverName.contains("SQL Server"))) {
					// "Microsoft SQL Server JDBC Driver 3.0" versus "Microsoft JDBC Driver 4.0 for SQL Server"
				useSetObject = true;
			}
			else if (databaseProductName.startsWith("DB2") ||
					jdbcDriverName.startsWith("jConnect") ||
					jdbcDriverName.startsWith("SQLServer")||
					jdbcDriverName.startsWith("Apache Derby")) {
				sqlTypeToUse = Types.VARCHAR;
			}
		}
		if (useSetObject) {
			ps.setObject(paramIndex, null);
		}
		else {
			ps.setNull(paramIndex, sqlTypeToUse);
		}
	}
	
}
